1651. Hopper Company Queries III.sql

WITH RECURSIVE months AS (
select1as m
union
select m+1as m
from months
where m<=11
),
cte AS (
SELECTmn.m,SUM(COALESCE(ar.ride_distance,0)) AS ride_distance,SUM(COALESCE(ar.ride_duration,0)) AS ride_duration,
 CASE WHEN m BETWEEN 1AND3 THEN 'q1'
 WHEN m BETWEEN 4AND6 THEN 'q2'
 WHEN m BETWEEN 7AND9 THEN 'q3'
 ELSE 'q4'
 END AS quater
FROM accepted_rides_1651 ar
INNER JOIN rides_1651 r ONar.ride_id=r.ride_idAND EXTRACT(year FROM requested_at) =2020
RIGHT JOIN months mn ONmn.m= EXTRACT(month FROM requested_at)
GROUP BYmn.m
)
SELECT m,
 ROUND(AVG(ride_distance) OVER (ORDER BY m ROWS BETWEEN CURRENT ROW AND2 FOLLOWING),2) AS average_ride_distance,
 ROUND(AVG(ride_duration) OVER (ORDER BY m ROWS BETWEEN CURRENT ROW AND2 FOLLOWING),2) AS average_ride_duration
FROM cte;